/*
	exec SP_CUSTOMER_REPORT_PROC 'cUSTOMER','0','0','active','0'
*/
alter PROC sp_customer_report_proc 

@CUSTOMER_TYPE		VARCHAR(50),
@CUSTOMER_IDS		VARCHAR(200),
@REGION			VARCHAR(50),
@STATUS			VARCHAR(50),
@DEFAULT_PROMPT		VARCHAR(50)	

AS
BEGIN
	DECLARE @CUSTOMERSTRING VARCHAR(4000)
	SET @CUSTOMERSTRING = N' 
	SELECT 
		C_NAME[Customer Name],C_NUMBER[Customer Number],C_TYPE_NAME [Customer Type],C_CONTACT_PERSON [Contact person],
		C_SHIPPING_METHOD_1 [Shipping Method],C_ACT_NUMBER_1 [Shipping Account],C_REGION [Region],NULL [Payment_terms],
		( case when C_DISABLED = 1 then ''Active'' else ''Disable'' end ) [Customer Status],
		DELIVERY_TERMS [Delivery Terms],A.ADDRESS_1 [Ship_Address_1],A.ADDRESS_2[Ship_Address_2],A.ADDRESS_3 [Ship_Address_3],
		A.ADDRESS_4 [Ship_Address_4],A.CITY [Ship_City],A.STATE [Ship_State],A.ZIPCODE [Ship_Zip],A.COUNTRY [Ship_Country],
		A.CONTACT_PERSON [Ship_Contact],ACPT.PHONE_NUMBER [Ship_Phone],ACPT.FAX [Ship_Fax],ACPT.EMAIL [Ship_Email],
		A1.ADDRESS_1 [Bill_Address_1],A1.ADDRESS_2[Bill_Address_2],A1.ADDRESS_3 [Bill_Address_3],
		A1.ADDRESS_4 [Bill_Address_4],A1.CITY [Bill_City],A1.STATE [Bill_State],A1.ZIPCODE [Bill_Zip],A1.COUNTRY [Bill_Country],
		A1.CONTACT_PERSON [Bill_Contact],ACPT1.PHONE_NUMBER [Bill_Phone],ACPT1.FAX [Bill_Fax],ACPT1.EMAIL [Bill_Email]
		
	--SELECT *
	 FROM 
		CUSTOMER C  INNER JOIN ADDRESS A ON C.C_DEFAULT_SHIPPING_ADDRESS_ID = A.ADDRESS_ID
			INNER JOIN ADDRESS A1 ON C.C_DEFAULT_BILLING_ADDRESS_ID = A1.ADDRESS_ID
		INNER JOIN CUSTOMER_TYPE_RELATIONSHIP CTR ON C.CUSTOMER_ID=CTR.CUSTOMER_ID
		INNER JOIN CUSTOMER_TYPES CT ON CTR.C_TYPE_ID = CT.C_TYPE_ID
		LEFT OUTER JOIN DELIVERY_TERMS DT ON C.C_DELIVERY_TERMS_ID = DT.DELIVERY_TERMS_ID 
		LEFT OUTER JOIN ADDRESS_CONTACT_PERSONS_TABLE ACPT ON A.address_id = ACPT.address_id AND A.CONTACT_PERSON = ACPT.CONTACT_PERSON
		LEFT OUTER JOIN ADDRESS_CONTACT_PERSONS_TABLE ACPT1 ON A1.address_id = ACPT1.address_id AND A1.CONTACT_PERSON = ACPT1.CONTACT_PERSON
	WHERE 	1=1 '

	IF @CUSTOMER_TYPE <> '0'
	BEGIN
		SET @CUSTOMERSTRING = @CUSTOMERSTRING + ' AND C_TYPE_NAME = '''+ @CUSTOMER_TYPE+''''
	END
	IF @CUSTOMER_IDS <> '0'
	BEGIN
		SET @CUSTOMERSTRING = @CUSTOMERSTRING + ' AND C.CUSTOMER_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ( '''+ @CUSTOMER_IDS+''''+') )'
	END
	IF @REGION <> '0'
	BEGIN
		SET @CUSTOMERSTRING = @CUSTOMERSTRING + ' AND C_REGION = '''+ @REGION+''''
	END
	IF @STATUS <> '0'
	BEGIN
		SET @CUSTOMERSTRING = @CUSTOMERSTRING + ' AND ( CASE WHEN C_DISABLED = 1 THEN ''ACTIVE'' ELSE ''DISABLE'' END ) = '''+ @STATUS+''''
	END
	SET @CUSTOMERSTRING = @CUSTOMERSTRING + ' order by C_NAME' 
	--PRINT '@CUSTOMERSTRING :'+@CUSTOMERSTRING
	EXEC (@CUSTOMERSTRING)
END